﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Maticsoft.DBUtility;
using System.Xml;
using System.Data;
using Xiaov.SmartWeb.Infrastructure.ExtMethod;

namespace Xiaov.SmartWeb.Model.Statistical
{
    public class GridData
    {
        public string QuerySQLText { get; set; }

        public string SelectField { get; set; }

        public string TableName { get; set; }

        public string DWBM { get; set; }

        public string Year { get; set; }

        public string Month { get; set; }

        public string OrderBy { get; set; }

        public DataSet Data { get; set; }

        public GridData(string _field, string _tn, string _dwbm, string _year, string _month, string _orderby)
        {
            SelectField = _field;            
            TableName = _tn;
            DWBM = _dwbm;
            Year = _year;
            Month = _month;
            OrderBy = (string.IsNullOrEmpty(_orderby) ? "hc" : _orderby);
            CreateSql();
            GetDataSet();
        }

        public GridData()
        {
            
        }

        public void CreateSql()
        {
            if (!string.IsNullOrEmpty(SelectField)&&SelectField.ToRequestString().ToUpper()!="NULL")
            {
                QuerySQLText = "select " + SelectField + " from " + TableName + " t where t.dwbm='" + DWBM + "' and t.nf='" + Year + "' and t.yf='" + Month + "' Order by " + OrderBy + " Asc";
                
            }
            else
                QuerySQLText = "select  t.*,t.rowid  from " + TableName + " t where t.dwbm='" + DWBM + "' and t.nf='" + Year + "' and t.yf='" + Month + "' Order by " + OrderBy + " Asc";
        }

        public System.Data.DataSet GetDataSet()
        {
            Data = DbHelperOra.Query(QuerySQLText);
            return Data;
        }

        public System.Data.DataTable GetEmptyData()
        {
            StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("select " + SelectField + " from " + TableName + " t where t.dwbm='" + DWBM + "' and t.nf='" + Year + "' and t.yf='" + Month + "' and rownum=1 Order by " + OrderBy + " Asc");

            DataSet ds = DbHelperOra.Query(sql.ToString());
            DataTable dt = ds.Tables[0].Copy();
            dt.Rows.Clear();
            return dt;
        }

        /// <summary>
        /// 保存数据表
        /// </summary>
        /// <param name="table">表</param>
        /// <param name="xml">数据xml格式</param>
        public void Save(string table, string xml)
        {
            //解析并保存
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(xml);
            XmlNodeList nodelist = xmlDoc.DocumentElement.SelectNodes("Record");
            string sql = "begin ";
            foreach (XmlNode node in nodelist)
            {
                sql += " update " + table + " set ";
                foreach (XmlElement child in node)
                {
                    if (child.Name.ToLower() != "rowid")
                        sql += child.Name + "='" + child.InnerText.Trim() + "',";
                }
                sql = sql.Remove(sql.Length - 1);
                sql += " where rowid='" + node.SelectSingleNode("ROWID").InnerText + "';";
            }
            sql += "null;end;";
            DbHelperOra.ExecuteSql(sql);
        }

        

        public static List<SSEC.DataBaseDict.Field> GetFileds(string sql)
        {
            List<SSEC.DataBaseDict.Field> list = new List<SSEC.DataBaseDict.Field>();
            System.Data.DataTable dt = DbHelperOra.Query(sql).Tables[0];
            if (dt != null)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    SSEC.DataBaseDict.Field field = new SSEC.DataBaseDict.Field();
                    field.FieldName = dc.ColumnName;
                    field.Type = dc.DataType.ToString();
                    list.Add(field);
                }
            }
            return list;
        }

        public static List<SSEC.DataBaseDict.Field> GetFileds(System.Data.DataTable dt)
        {
            List<SSEC.DataBaseDict.Field> list = new List<SSEC.DataBaseDict.Field>();
            if (dt != null)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    SSEC.DataBaseDict.Field field = new SSEC.DataBaseDict.Field();
                    field.FieldName = dc.ColumnName;
                    field.Type = dc.DataType.ToString();
                    list.Add(field);
                }
            }
            return list;
        }

        public void DeleteRow(string TableName, string ROWID)
        {
            string sql = "Delete from "+TableName+" t where  t.rowid='"+ROWID+"'";
            DbHelperOra.ExecuteSql(sql);
        }

        #region NH68 / TG85 保存特殊处理

        public void SaveNH68(string table, string xml, string dwbm, string year, string month)
        {
            //解析并保存
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(xml);
            XmlNodeList nodelist = xmlDoc.DocumentElement.SelectNodes("Record");
            string sql = "begin ";
            foreach (XmlNode node in nodelist)
            {
                //第一组
                sql += " update " + table + " set BGQ=" + node.SelectSingleNode("BGQ1").InnerXml.ToOracleDBString() + ",SNTQ=" + node.SelectSingleNode("SNTQ1").InnerXml.ToOracleDBString() + "";
                sql += " where ZBBM='" + node.SelectSingleNode("ZBBM1").InnerText + "' and DWBM='" + dwbm + "' and NF='" + year + "' and YF='" + month + "';";
                //No.2
                sql += " update " + table + " set BGQ=" + node.SelectSingleNode("BGQ3").InnerXml.ToOracleDBString() + ",SNTQ=" + node.SelectSingleNode("SNTQ3").InnerXml.ToOracleDBString() + "";
                sql += " where ZBBM='" + node.SelectSingleNode("ZBBM3").InnerText + "' and DWBM='" + dwbm + "' and NF='" + year + "' and YF='" + month + "';";
                //No.3
                sql += " update " + table + " set BGQ=" + node.SelectSingleNode("BGQ4").InnerXml.ToOracleDBString() + ",SNTQ=" + node.SelectSingleNode("SNTQ4").InnerXml.ToOracleDBString() + "";
                sql += " where ZBBM='" + node.SelectSingleNode("ZBBM4").InnerText + "' and DWBM='" + dwbm + "' and NF='" + year + "' and YF='" + month + "';";
            }
            sql += "null;end;";
            DbHelperOra.ExecuteSql(sql);
        }
        public void SaveTG85(string table, string xml, string dwbm, string year, string month)
        {
            //解析并保存
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(xml);
            XmlNodeList nodelist = xmlDoc.DocumentElement.SelectNodes("Record");
            string sql = "begin ";
            foreach (XmlNode node in nodelist)
            {
                //第一组
                sql += " update " + table + " set SL=" + node.SelectSingleNode("SL1").InnerXml.ToOracleDBString();
                sql += " where ZBBM='" + node.SelectSingleNode("ZBBM1").InnerText + "' and DWBM='" + dwbm + "' and NF='" + year + "' and YF='" + month + "';";
                //No.2
                sql += " update " + table + " set SL=" + node.SelectSingleNode("SL2").InnerXml.ToOracleDBString();
                sql += " where ZBBM='" + node.SelectSingleNode("ZBBM2").InnerText + "' and DWBM='" + dwbm + "' and NF='" + year + "' and YF='" + month + "';";
                //No.3
                sql += " update " + table + " set SL=" + node.SelectSingleNode("SL3").InnerXml.ToOracleDBString() ;
                sql += " where ZBBM='" + node.SelectSingleNode("ZBBM3").InnerText + "' and DWBM='" + dwbm + "' and NF='" + year + "' and YF='" + month + "';";
            }
            sql += "null;end;";
            DbHelperOra.ExecuteSql(sql);
        }
        #endregion
    }
}
